﻿using EPPluslHelper;
using ExcelProject.Model;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ExcelProject.Core.Inter
{
    public class ExcelService : IExcelService
    {
        /// <summary>
        /// 设置Sheet基本信息
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheeName">Shee名称</param>
        /// <param name="tableHeaderX">X轴的区间</param>
        /// <param name="tableHeaderY">T轴的区间</param>
        /// <returns></returns>
        public List<T> GetSheetData<T>(ExcelConfig config, T model)
        {
            if (config.SheetList == null || model==null) 
            {
                return null;
            }

            try
            {
                var map = ExcelCellOption<T>.GenExcelOption(c => 1);
                Type t = model.GetType();
                PropertyInfo[] PropertyList = t.GetProperties();
                foreach (PropertyInfo item in PropertyList)
                {
                    map.AddOption(item);
                }
                var result = EPPlusExcelToEntity(config.SelectSheet, config.TableHeaderX, config.TableHeaderY, map);
                return result;
            }
            catch (Exception)
            {
                return null;
            }
        }
        
        /// <summary>
        /// 读取指定Sheet的数据
        /// </summary>
        /// <typeparam name="T">模型类型</typeparam>
        /// <param name="config">基本信息</param>
        /// <param name="t">对象</param>
        /// <returns></returns>
        public ExcelConfig ReadSheet(string path, string sheetName, List<int> tableHeaderX, List<int> tableHeaderY)
        {
            try
            {
                FileInfo file = new FileInfo(path);
                ExcelPackage excelPackage = new ExcelPackage(file);
                
                    ExcelWorksheets sheetList = excelPackage.Workbook.Worksheets;
                    if (sheetList.Count > 0)
                    {
                        //指定需要读入的sheet名
                        ExcelWorksheet workSheet = sheetList[sheetName];
                        if (workSheet != null)
                        {
                            return new ExcelConfig()
                            {
                                Path = path,
                                SheetName = sheetName,
                                TableHeaderX = tableHeaderX,
                                TableHeaderY = tableHeaderY,
                                SelectSheet = workSheet,
                                SheetList = sheetList
                            };
                        }
                    }
                
            }
            catch (Exception)
            {
                return null;
            }
            return null;
        }
        

        /// <summary>
        /// 将表格数据转换为指定的数据实体
        /// </summary>
        private List<T> EPPlusExcelToEntity<T>(ExcelWorksheet workSheet, List<int> tableHeaderX, List<int> tableHeaderY, ICollection<ExcelCellOption<T>> options)
        {

            List<string> Column = new();
            List<T> data = new();
            int rowCount = workSheet.Dimension.Rows, colCount = tableHeaderY.Max();
            #region 合并单元格坐标存储
            //合并单元格的坐标
            List<MergedAddress> MergedCellsList = new();
            //所有合并单元格
            var MergedCells = workSheet.MergedCells;
            foreach (var item in MergedCells)
            {

                var cell = workSheet.Cells[item];   //获取合并单元格
                var index = item.IndexOf(":");

                #region 合并单元格基本信息
                MergedAddress merged = new MergedAddress()
                {
                    Address = item,
                    Begin = item.Substring(0, index),
                    End = item.Substring(index, 2),
                    StartRow = cell.Start.Row,
                    StartColumn = cell.Start.Column,
                    EndRow = cell.End.Row,
                    EndColumn = cell.End.Column
                };
                #endregion

                #region 检查类型
                //开始列与结束列相同，则说明是在跨行
                if (cell.Start.Column == cell.End.Column)
                {
                    merged.Type = CellMergeType.跨行;
                }
                //开始行与结束行相同，则说明是在跨列
                if (cell.Start.Row == cell.End.Row)
                {
                    merged.Type = CellMergeType.跨列;
                }
                //起始行与列皆不相同，则说明是在跨行跨列
                if (cell.Start.Column != cell.End.Column && cell.Start.Row != cell.End.Row)
                {
                    merged.Type = CellMergeType.跨行跨列;
                }
                MergedCellsList.Add(merged);
                #endregion
            }

            #endregion

            #region 读取表格,按行读取
            //循环列(x轴)
            for (int m = workSheet.Dimension.Start.Row, n = rowCount; m <= n; m++)
            {
                Dictionary<string, string> keyValuePairs = new Dictionary<string, string>();

                //循环行(y轴)
                for (int j = workSheet.Dimension.Start.Column, k = colCount; j <= k; j++)
                {
                    var Cell = workSheet.Cells[m, j];      //单元格
                    var CellValue = workSheet.Cells[m, j].Value;        //单元格值


                    if (CellValue != null)
                    {
                        //是否是表头
                        if (tableHeaderX.Contains(m) && tableHeaderY.Contains(j))
                        {
                            if (Cell.Merge)
                            {
                                Column.Add(Replace($"{CellValue}"));
                            }
                            else
                            {
                                //包含在表头数组的话

                                //如果我的上一行不是跨列，那么我大概率是数据，而不是表头
                                int x = m - 1;      //行-1，找当前单元格上面的格子
                                int y = j;          //列

                                //取到跨列的坐标集,测试数据暂且用Fist取，实际会存在多个坐标对象
                                var parent = MergedCellsList.FirstOrDefault(c => c.Type == CellMergeType.跨列 && x == c.StartRow && (y >= c.StartColumn && y <= c.EndColumn));
                                if (parent != null)
                                {
                                    //如果我的上一行单元格是跨列，那么我就要变成子集。
                                    //txtMsg.Text += $"\n{m}行{j}列 | 非合并单元格 | 列名称:{Replace(CellValue)} | 父级信息:[{workSheet.Cells[parent.Address].Text}]";
                                    Column.Add(Replace($"{workSheet.Cells[parent.Address].Text}_{CellValue}"));
                                    continue;
                                }
                            }

                        }
                        //大于X轴或者大于Y轴则说明是数据
                        else if (m <= rowCount && m >= tableHeaderY.Max() || j <= colCount && m >= tableHeaderX.Max())
                        {
                            keyValuePairs.Add(Column[Cell.Start.Column - 1], Replace($"{Replace($"{CellValue}")}"));
                        }
                       
                    }
                }
                var obj = Activator.CreateInstance(typeof(T));
                foreach (var option in options)
                {
                    if (!string.IsNullOrEmpty(option.ExcelField))
                    {
                        var value = keyValuePairs.ContainsKey(option.ExcelField) ? keyValuePairs[option.ExcelField] : string.Empty;
                        if (!string.IsNullOrEmpty(value))
                            option.Prop.SetValue(obj, option.Action == null ? value : option.Action(value));
                    }
                    // 可以用来初始化与表格无关的字段 如 创建时间 Guid主键 之类的东西
                    else
                        option.Prop.SetValue(obj, option.Action == null ? null : option.Action(string.Empty));
                }
                if (options!=null) 
                {
                    data.Add((T)obj);
                }
                
            }
            
            #endregion
            return data;
        }

        private static string Replace(object str)
        {
            if (str != null)
            {
                return str.ToString().Replace("\n", "");
            }
            return "";
        }
    }
}
